Imports System.Data.SqlClient
Public Class DataAccess
    Dim miConexion As SqlConnection
    Dim miCadena As String = Global.Inmobiliaria.My.Settings.inmobiliariaConnectionString
    Dim miPath As String = "..\..\imagenes"
    'Dim miPath As String = "C:\Users\Mateo Guzman\Documents\Faku\PAV1\Inmobiliaria\Fuente\Inmobiliaria\imagenes"
    'Dim miPath As String = "C:\Documents and Settings\Rodrigo G. Gil\Escritorio\FUENTE 1.3.4"
    Dim miCommand As New SqlCommand
    Public Property Cadena() As String
        Get
            Return Me.miCadena
        End Get
        Set(ByVal value As String)
            Me.miCadena = value
        End Set
    End Property
    Public ReadOnly Property path() As String
        Get
            Return miPath
        End Get
    End Property
    Public Sub New()

    End Sub
    Public Sub New(ByVal pCadena As String)
        Me.miCadena = pCadena
    End Sub
    Private Sub conectar()
        Me.miConexion = New SqlConnection(Me.miCadena)
        Me.miConexion.Open()
        Me.miCommand.Connection = Me.miConexion
    End Sub
    Private Sub desconectar()
        Me.miConexion.Close()
    End Sub
    Public Function ejecutarSQL(ByVal sql As String) As Boolean
        Me.conectar()
        Me.miCommand.CommandType = CommandType.Text
        Me.miCommand.CommandText = sql
        Me.miCommand.ExecuteNonQuery()
        Me.desconectar()
    End Function
    Public Function consultarSQL(ByVal sql As String) As DataTable
        Dim tabla As New DataTable
        Me.conectar()
        Me.miCommand.CommandType = CommandType.Text
        Me.miCommand.CommandText = sql
        tabla.Load(Me.miCommand.ExecuteReader())
        Me.desconectar()
        Return tabla
    End Function
    Public Sub cargarDataSet(ByVal sql As String, ByRef dt As DataTable)
        Me.conectar()
        Me.miCommand.CommandType = CommandType.Text
        Me.miCommand.CommandText = sql
        dt.Load(Me.miCommand.ExecuteReader())
        Me.desconectar()
    End Sub

    Public Function ejecutarSP(ByVal pCommand As SqlCommand) As Boolean
        Me.conectar()
        Me.miCommand = pCommand
        Me.miCommand.Connection = Me.miConexion

        Me.miCommand.CommandType = CommandType.StoredProcedure
        Dim resp As Boolean
        If Me.miCommand.ExecuteNonQuery() > 0 Then
            resp = True
        Else
            resp = False
        End If
        Me.desconectar()
        Return resp
    End Function
    Public Function consultarSP(ByVal pCommand As SqlCommand) As DataTable
        Dim tabla As New DataTable
        Me.conectar()
        Me.miCommand = pCommand
        Me.miCommand.CommandType = CommandType.StoredProcedure
        Me.miCommand.Connection = Me.miConexion
        tabla.Load(Me.miCommand.ExecuteReader())
        Me.desconectar()
        Return tabla
    End Function
    Public Function consultarSP(ByVal pProcedimiento As String) As DataTable
        Dim tabla As New DataTable
        Me.conectar()
        Me.miCommand.CommandType = CommandType.StoredProcedure
        Me.miCommand.CommandText = pProcedimiento
        tabla.Load(Me.miCommand.ExecuteReader())
        Me.desconectar()
        Return tabla
    End Function

    Public Function leo_tabla(ByVal nombre_tabla As String) As Data.DataTable
        Return Me.consultarSQL("select * from " & nombre_tabla)
    End Function
    Public Function leo_tabla(ByVal nombre_tabla As String, ByVal pk As String, ByVal descripcion As String) As Data.DataTable
        Return Me.consultarSQL("select " & pk & ", " & descripcion & " from " & nombre_tabla)
    End Function
    Public Function leo_tabla2(ByVal nombre_tabla As String, ByVal pk As String, ByVal descripcion As String) As Data.DataTable
        Dim tabla As New DataTable
        Dim col1 As New DataColumn(pk)
        Dim col2 As New DataColumn(descripcion)
        tabla.Columns.Add(col1)
        tabla.Columns.Add(col2)
        Dim row As DataRow = tabla.NewRow
        row.Item(0) = -1
        row.Item(1) = "--Seleccione--"
        tabla.Rows.Add(row)
        tabla.Merge(Me.consultarSQL("select " & pk & ", " & descripcion & " from " & nombre_tabla))
        Return tabla
    End Function
    Public Function leerColumnasTabla(ByVal nombreTabla As String) As DataTable
        Dim tabla As New DataTable
        Me.conectar()
        Try
            Me.miCommand.CommandType = CommandType.StoredProcedure
            Me.miCommand.CommandText = "usp_obtenerColumnas"
            Me.miCommand.Parameters.AddWithValue("tabla", nombreTabla)
            tabla.Load(Me.miCommand.ExecuteReader)
        Catch ex As Exception
            Me.miCommand.CommandType = CommandType.Text
            Me.miCommand.CommandText = "  select COLUMN_NAME columna, ORDINAL_POSITION id from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = " + nombreTabla
            tabla.Load(Me.miCommand.ExecuteReader)
        End Try
       
        Return tabla
    End Function
    Public Sub soloNumeros(ByRef e As Char)
        Select Case Asc(e)
            Case 8, 24, 27, 19, 127, 13, 9, 15, 14
                Exit Sub
        End Select
        If IsNumeric(e) = False Then
            e = ""
        End If
    End Sub
    Public Sub soloNumerosYPuntos(ByRef e As Char)
        Select Case Asc(e)
            Case 8, 24, 27, 19, 127, 13, 9, 15, 14, 46
                Exit Sub
        End Select
        If IsNumeric(e) = False Then
            e = ""
        End If
    End Sub

End Class
